-- **************************************************************************
--    Project Name:   经营指标-退件签收预警
--    Job Name:       jms_dm__dm_reback_transfer_warming_waybill_detail_dt
--    Author :        季修魁
--    date：          2023/12/06
--    description：   jar调整为hql,新增业务需求优化逻辑
-- **************************************************************************
-- **************************************************************************

--网点资料表
with t_network as (
select code             --网点code
       ,name            --网点名字
       ,manage_code     --管理大区名称'
       ,manage_name     --管理大区名字
       ,agent_id        --代理区id
       ,agent_code      --代理区code
       ,agent_name      --代理区name
       ,fran_code       --加盟商code
       ,fran_name       --加盟商名字
       ,provider_id     --省份id
       ,provider_desc   --省份
       ,city_id         --城市id
       ,city_desc       --城市
       ,area_id         --区域id
       ,area_desc       --区域
       ,network_type  as station_type   --'网点类型:1-总部,2-代理区,3-加盟商,4-中心 ,5-集散点,6-网点'
  from jms_dim.dim_network_whole_massage
),

--人员表,获取派件员信息
t_staff as (
select id,
	  code,
	  name
from (
	 select id,
			code,
			name,
			row_number() over (partition by code order by update_time desc) bill_rank
	 from jms_dim.dim_lmdm_sys_staff
   ) t where bill_rank = 1
),

--区域表,获取出仓乡镇名称
t_area as (
select id,
	code,
	cn_name
from jms_dim.dim_lmdm_sys_area
),

--散货时效,通过中心集散发件日期、发件网点、到件网点关联获取对应日期线路的规划签收时间
t_effective as (
select depart_network_code,--发件网点
       arrive_network_code,--到件网点
       concat(planned_delivery_time,':00') as planned_delivery_time,--规划发件时间
       case when replace(planned_arrival_time,':','')>='0000' and replace(planned_arrival_time,':','')<='0630'  then '10:30:00'
            when replace(planned_arrival_time,':','')>'0630' and replace(planned_arrival_time,':','')<='0730' then '11:30:00'
            when replace(planned_arrival_time,':','')>'0730' and replace(planned_arrival_time,':','')<='0930' then '14:00:00'
            when replace(planned_arrival_time,':','')>'0930' and replace(planned_arrival_time,':','')<='1400' then '18:00:00'
            when replace(planned_arrival_time,':','')>'1400' and replace(planned_arrival_time,':','')<='2400' then '22:00:00'
            ELSE ''
       END AS  deadline_signing_time,--规划签收截止时间,旧的数据精确到分钟,需要拼接到完整的秒,新的数据已经到秒,不用拼接
       span_days_arrive,--跨越天数_到车
       nvl(out_line_day,0) as out_line_day,--派件班次-支线衔接跨天
       span_days_shift,--派件跨越天数
       span_days_sign,--跨越天数_签收
       shift,--班次
       create_time,--创建时间
       effective_date,--生效时间
       expiration_date--失效时间
       ---to_date(substr('{{ execution_date | cst_hour }}',1,10)) as create_date
 from jms_dim.dim_tmsnew_time_effective_base_hi
where is_enable = 1--是否启用 1 启用
  and line_type = 2--线路类型(1集货交件、2散货派件)
  and to_date(effective_date) <=to_date(substr('{{ execution_date | cst_hour }}',1,10))
  and to_date(expiration_date) >= to_date(substr('{{ execution_date | cst_hour }}',1,10))  --modify jixk 20230625
 ),

--特殊乡镇加时,对应网点/乡镇/班次有乡镇加时的,规划签收时间要加上乡镇加时
t_special as (
select network_id,
       --network_code as network_id,
       extra_time,
       shift,
       township_id
from (
select network_id,--网点id
	   network_code, --网点code
	   extra_time,--乡镇加时 天
	   shift,--派件班次
	   township_id,--乡镇id
	   row_number() over (partition by network_id,township_id,shift order by update_time desc) bill_rank
 from jms_dim.dim_lmdm_sys_special_time_limit_base
where  extra_time_type = 2--加时类型:1收件加时,2派件加时
  and is_enable=1
   ) t where bill_rank = 1
),

--末端网点发件、获取网点退转寄后是否做了发件扫描
t_network_send as (
select billcode,
	 scansitecode,
	 case when length(max(wid_columns)[0]) = 0 then null else max(wid_columns)[0] end lst_end_network_send_time  --最后一次网点发件时间
from (
	select
		   waybill_no as billcode,
		   scan_site_code as scansitecode,
		   split(concat(scan_time,'\001',
		   coalesce(scan_site_code,'')
		   ),'\001') as wid_columns
	from jms_dwd.dwd_tab_barscan_sitesend_base_hi      --15天内有效
	left join t_network t_network1 on scan_site_code = t_network1.code
	where dt between date_format(date_add(substr('{{ execution_date | cst_hour }}',1,10), -1), 'yyyy-MM-dd-00')   --modify jixk 20230509
	  and date_format(date_add(substr('{{ execution_date | cst_hour }}',1,10), 1), 'yyyy-MM-dd-00')
	  and length(waybill_no) = 15
	  and t_network1.station_type = 6
	) t group by billcode,scansitecode
),

--退回转寄件,关联网点发件,判断是网点退转扫描后是否有做发件
t_reback_transfer as (
select billcode,                   --运单号
	 print_network_code,         --打印网点编码
	 print_network_name,         --打印网点
	 case when lst_end_network_send_time > print_time then 1 else 2 end is_send,--网点做退转寄之后是否有做发件
	 lst_end_network_send_time,  --最后一次网点发件时间
	 entry_type ,                 --退件类型 1拦截退转 2问题件退转
	 apply_network_id,           --���记网点ID
	 apply_network_code,         --登记网点code
	 apply_network_name,         --登记网点名字
	 print_time,                 --退转打印时间
	 date_format(apply_time,'yyyy-MM-dd-00 HH:mm:ss') as reback_transfer_apply_time  --退转申请时间
from (
	select waybill_no billcode,        --运单号
		   print_network_code,         --打印网点
		   print_network_name,         --打印网点名字
		   print_time,                 --面单打印时间
		   apply_time,                 --退件申请时间
		   lst_end_network_send_time,  --最后一次网点发件时间
		   case when entry_type=1 then 1
				when entry_type=2 and t_network.station_type =6 then 2
				when entry_type=2 and t_network.station_type =4 then 3
				when entry_type=2 and t_network.station_type =5 then 4 end as  entry_type,            --退件类型 1拦截退转 2问题件退转
		   apply_network_id,           --登记网点ID
		   apply_network_code,         --登记网点code
		   apply_network_name,         --登记网点名字
		   row_number() over (partition by waybill_no order by apply_time desc) bill_rank
	from jms_dwd.dwd_tab_reback_transfer_express_base_hi t_reback_transfer
	left join t_network_send on t_reback_transfer.waybill_no = t_network_send.billcode and t_reback_transfer.print_network_code = t_network_send.scansitecode
	join t_network on t_reback_transfer.apply_network_code=t_network.code
	where dt between date_format(date_add(substr('{{ execution_date | cst_hour }}',1,10), -35), 'yyyy-MM-dd')
	   and date_format(date_add(substr('{{ execution_date | cst_hour }}',1,10), 0), 'yyyy-MM-dd')
	  and to_date(print_time) between date_format(date_add(substr('{{ execution_date | cst_hour }}',1,10), -30), 'yyyy-MM-dd')
	  and date_format(date_add(substr('{{ execution_date | cst_hour }}',1,10), 1), 'yyyy-MM-dd')
	  and length(waybill_no) = 15
	  and origin_network_flag=1   -- 增加只退回揽收网点条件 2022-09-17
	 ) t where bill_rank = 1
),

--末端中心/集散发网点的发件数据,按运单号去重,签收、出库之后的操作数据不参与计算,每天重新计��最近10天的数据
t_send as (
select billcode,
 case when length(end_center_send_time) = 0 then null else end_center_send_time end end_center_send_time,                     --末端中心发件时间
 case when length(end_center_code) = 0 then null else end_center_code end end_center_code,                                    --末端中心code
 case when length(send_next_station_code) = 0 then null else send_next_station_code end send_next_station_code,               --发往下一站code
 case when length(fst_end_center_send_time) = 0 then null else fst_end_center_send_time end fst_end_center_send_time,         --末端中心第一次发件时间
 case when length(fst_end_center_code) = 0 then null else fst_end_center_code end fst_end_center_code,                        --第一次末端中心code
 case when length(fst_send_next_station_code) = 0 then null else fst_send_next_station_code end fst_send_next_station_code,   --第一次末端中心name
 case when length(lst_end_center_send_time) = 0 then null else lst_end_center_send_time end lst_end_center_send_time,         --最后一次末端中心发件时间
 case when length(lst_end_center_code) = 0 then null else lst_end_center_code end lst_end_center_code,                        --最后一次末端中心code
 case when length(lst_send_next_station_code) = 0 then null else lst_send_next_station_code end lst_send_next_station_code    --最后一次末端中心名字
from (
select billcode,
	   case when min(wid_columns)[1] = max(wid_columns)[1] and min(wid_columns)[2] = max(wid_columns)[2] then min(wid_columns)[0] else max(wid_columns)[0] end end_center_send_time,                               --末端中心发件时间 相同取最早不同取最新
				case when min(wid_columns)[1] = max(wid_columns)[1] and min(wid_columns)[2] = max(wid_columns)[2] then min(wid_columns)[1] else max(wid_columns)[1] end end_center_code,                                    --末端中心code 相同取最早不同取最新
				case when min(wid_columns)[1] = max(wid_columns)[1] and min(wid_columns)[2] = max(wid_columns)[2] then min(wid_columns)[2] else max(wid_columns)[2] end send_next_station_code,                             --末端中心发往下一站code 相同取最早不同取最新
				min(wid_columns)[0] as fst_end_center_send_time,    --第一次末端中心发件时间
				min(wid_columns)[1] as fst_end_center_code,         --第一次末端中心code
				min(wid_columns)[2] as fst_send_next_station_code,  --第一次末端中心发往下一站code
				max(wid_columns)[0] as lst_end_center_send_time,    --最后一次末端中心发件时间-
				max(wid_columns)[1] as lst_end_center_code,         --最后一次末端中心code
				max(wid_columns)[2] as lst_send_next_station_code   --最后一次末端中心发往下一站code
		 from (
			   select
					  t_send.waybill_no as billcode,
					  split(concat(scan_time,'\001',
					  coalesce(scan_site_code,''),'\001',
					  coalesce(next_station_code,'')
					  ),'\001') as wid_columns
			   from jms_dwd.dwd_tab_barscan_send_base_hi t_send     --15天内有效
			   left join (
						  select billcode,
							 min(scantime) as actual_sign_scan_time
						  from (
								select waybill_no as billcode,
									   signed_time as scantime
								from jms_dwd.dwd_tab_barscan_sign_base_hi
								where dt between date_format(date_add(substr('{{ execution_date | cst_hour }}',1,10), -1), 'yyyy-MM-dd-00') --modify jixk 20230509
								  and date_format(date_add(substr('{{ execution_date | cst_hour }}',1,10), 1), 'yyyy-MM-dd-00')
								  and length(waybill_no) = 15
								union all
								select waybill_no as billcode,
									   scan_time as scantime
								from jms_dwd.dwd_tab_barscan_collect_base_hi
								where dt between date_format(date_add(substr('{{ execution_date | cst_hour }}',1,10), -1), 'yyyy-MM-dd-00') --modify jixk 20230509
								   and date_format(date_add(substr('{{ execution_date | cst_hour }}',1,10), 1), 'yyyy-MM-dd-00')
								  and length(waybill_no) = 15
								  and scan_type = '出库扫描'
							   ) t group by billcode
							) t_actual_sign on t_send.waybill_no = t_actual_sign.billcode
			   left join t_network t_network1 on scan_site_code = t_network1.code
			   left join t_network t_network2 on next_station_code = t_network2.code
			   join t_reback_transfer on t_send.waybill_no=t_reback_transfer.billcode
			   where dt between date_format(date_add(substr('{{ execution_date | cst_hour }}',1,10), -1), 'yyyy-MM-dd-00')  --modify jixk 20230509
				 and date_format(date_add(substr('{{ execution_date | cst_hour }}',1,10), 1), 'yyyy-MM-dd-00')
				 and length(t_send.waybill_no) = 15
				 and t_send.scan_time <= nvl(t_actual_sign.actual_sign_scan_time,'9999-99-99')
				 and t_network1.station_type in (4,5)--发件站点是中心/计集散
				 and t_network2.station_type = 6--发件下一站是网点
				 and t_send.scan_time >=t_reback_transfer.reback_transfer_apply_time
			   ) t group by billcode
		) t where to_date(end_center_send_time) >= date_format(date_add(substr('{{ execution_date | cst_hour }}',1,10), -1), 'yyyy-MM-dd')  --modify jixk 20230509
			  and to_date(end_center_send_time) <= date_format(date_add(substr('{{ execution_date | cst_hour }}',1,10), 0), 'yyyy-MM-dd')   --modify jixk 20230509
),

--末端网点到件
t_arrival as (
	 select billcode,
			case when min(wid_columns)[1] = max(wid_columns)[1] then 2 else 1 end is_arrival_repeatedly,--是否多次到件 1 是 2 否
			case when length(min(wid_columns)[0]) = 0 then null else min(wid_columns)[0] end fst_end_network_arrival_time,  --第一次末端网点到件时间
			case when length(min(wid_columns)[1]) = 0 then null else min(wid_columns)[1] end fst_end_network_code,          --第一次末端网点到件code
			case when length(min(wid_columns)[2]) = 0 then null else min(wid_columns)[2] end fst_end_network_source,
			case when length(max(wid_columns)[0]) = 0 then null else max(wid_columns)[0] end lst_end_network_arrival_time,
			case when length(max(wid_columns)[1]) = 0 then null else max(wid_columns)[1] end lst_end_network_code,
			case when length(max(wid_columns)[2]) = 0 then null else max(wid_columns)[2] end lst_end_network_source
	 from (
		   select
				  a.waybill_no as billcode,
				  split(concat(a.scan_time,'\001',
				  coalesce(a.scan_site_code,''),'\001',
				  coalesce(a.source,'')
				  ),'\001') as wid_columns
		   from jms_dwd.dwd_tab_barscan_sitearrival_base_hi  a    --15天内有效
		   left join t_network t_network1 on a.scan_site_code = t_network1.code
		   join t_reback_transfer on a.waybill_no=t_reback_transfer.billcode
		   where dt between date_format(date_add(substr('{{ execution_date | cst_hour }}',1,10), -1), 'yyyy-MM-dd-00') --modify jixk 20230509
			 and date_format(date_add(substr('{{ execution_date | cst_hour }}',1,10), 1), 'yyyy-MM-dd-00')
			 and length(waybill_no) = 15
			 and t_network1.station_type = 6
			 and a.scan_time >=t_reback_transfer.reback_transfer_apply_time
		   ) t group by billcode
),

--出仓扫描
t_deliver as (
select billcode,
	  case when min(wid_columns)[2] = max(wid_columns)[2] then 2 else 1 end is_deliver_repeatedly,--是否多次出仓 1 是 2 否
	  case when length(min(wid_columns)[0]) = 0 then null else min(wid_columns)[0] end fst_deliver_time,
	  case when length(min(wid_columns)[1]) = 0 then null else min(wid_columns)[1] end fst_deliver_network_id,
	  case when length(min(wid_columns)[2]) = 0 then null else min(wid_columns)[2] end fst_deliver_network_code,
	  case when length(min(wid_columns)[3]) = 0 then null else min(wid_columns)[3] end fst_deliver_user_code,
	  case when length(min(wid_columns)[4]) = 0 then null else min(wid_columns)[4] end fst_deliver_unrecordflag,--未录单标识
	  case when length(min(wid_columns)[5]) = 0 then null else min(wid_columns)[5] end fst_deliver_township_id,--出仓乡镇id
	  case when length(min(wid_columns)[6]) = 0 then null else min(wid_columns)[6] end fst_deliver_source,--出仓来源

      case when length(min(wid_columns)[7]) = 0 then null else min(wid_columns)[7] end fst_deliver_scan_user_code,--新增出仓扫描员code
      case when length(min(wid_columns)[8]) = 0 then null else min(wid_columns)[8] end fst_deliver_check_flag, --新增核算标���

	  case when length(max(wid_columns)[0]) = 0 then null else max(wid_columns)[0] end lst_deliver_time,
	  case when length(max(wid_columns)[1]) = 0 then null else max(wid_columns)[1] end lst_deliver_network_id,
	  case when length(max(wid_columns)[2]) = 0 then null else max(wid_columns)[2] end lst_deliver_network_code,
	  case when length(max(wid_columns)[3]) = 0 then null else max(wid_columns)[3] end lst_deliver_user_code,
	  case when length(max(wid_columns)[4]) = 0 then null else max(wid_columns)[4] end lst_deliver_unrecordflag,
	  case when length(max(wid_columns)[5]) = 0 then null else max(wid_columns)[5] end lst_deliver_township_id,
	  case when length(max(wid_columns)[6]) = 0 then null else max(wid_columns)[6] end lst_deliver_source,

	case when length(max(wid_columns)[7]) = 0 then null else max(wid_columns)[7] end lst_deliver_scan_user_code,   --新增出仓扫描员code
	case when length(max(wid_columns)[8]) = 0 then null else max(wid_columns)[8] end lst_deliver_check_flag   --新增��算标识

from (
	 select  waybill_no as billcode,
			split(concat
			(scan_time,'\001',
			coalesce(scan_site_id,''),'\001',
			coalesce(scan_site_code,''),'\001',
			coalesce(send_user_code,''),'\001',
			coalesce(unrecord_flag,''),'\001',
			coalesce(width,''),'\001',
			coalesce(source,''),'\001',
		  coalesce(scan_user_code,''),'\001',
		  coalesce(check_flag,'')),'\001'
			) as wid_columns
	 from jms_dwd.dwd_tab_barscan_deliver_base_hi a     --15天内有效
	 join t_reback_transfer on a.waybill_no=t_reback_transfer.billcode
	 where dt between date_format(date_add(substr('{{ execution_date | cst_hour }}',1,10), -1), 'yyyy-MM-dd-00')  --modify jixk 20230509
	   and date_format(date_add(substr('{{ execution_date | cst_hour }}',1,10), 1), 'yyyy-MM-dd-00')
	   and length(a.waybill_no) = 15
	   and a.scan_time>=t_reback_transfer.reback_transfer_apply_time
	 ) t
	 group by billcode
),

--时效签收
t_aging_sign as (
select billcode,
       case when min(wid_columns)[1] = max(wid_columns)[1] then 2 else 1 end is_aging_repeatedly,
  case when length(min(wid_columns)[0]) = 0 then null else min(wid_columns)[0] end fst_aging_sign_time,         --第一次时效签收时间
  case when length(min(wid_columns)[1]) = 0 then null else min(wid_columns)[1] end fst_aging_sign_network_code, --第一次时效签收网点code
  case when length(min(wid_columns)[2]) = 0 then null else min(wid_columns)[2] end fst_aging_sign_scan_type,    --第一次时效签收类型
  case when length(min(wid_columns)[3]) = 0 then null else min(wid_columns)[3] end fst_aging_sign_user_code,     --
  case when length(min(wid_columns)[4]) = 0 then null else min(wid_columns)[4] end fst_aging_sign_pistolid,--巴枪id PDD开头是桃花岛其他是自助对接
  case when length(max(wid_columns)[0]) = 0 then null else max(wid_columns)[0] end lst_aging_sign_time,
  case when length(max(wid_columns)[1]) = 0 then null else max(wid_columns)[1] end lst_aging_sign_network_code,
  case when length(max(wid_columns)[2]) = 0 then null else max(wid_columns)[2] end lst_aging_sign_scan_type,
  case when length(max(wid_columns)[3]) = 0 then null else max(wid_columns)[3] end lst_aging_sign_user_code,
  case when length(max(wid_columns)[4]) = 0 then null else max(wid_columns)[4] end lst_aging_sign_pistolid
from (
 select t.billcode,
		split(concat(t.scantime,'\001',
		coalesce(t.scansitecode,''),'\001',
		coalesce(t.scantype,''),'\001',
		coalesce(t.send_deliver_usercode,''),'\001',
		coalesce(t.pistolid,'')
		),'\001') as wid_columns
 from (
	   select waybill_no as billcode,
			  signed_time as scantime,  --派件时间
			  signed_site_code as scansitecode,
			  '快件签收' as scantype,
			  send_user_code as send_deliver_usercode,
			  null as pistolid
	   from jms_dwd.dwd_tab_barscan_sign_base_hi
	   where dt between date_format(date_add(substr('{{ execution_date | cst_hour }}',1,10), -1), 'yyyy-MM-dd-00')  --modify jixk 20230509
		 and date_format(date_add(substr('{{ execution_date | cst_hour }}',1,10), 1), 'yyyy-MM-dd-00')
		 and length(waybill_no) = 15
	   union all
	   select waybill_no as billcode,
			scan_time as   scantime,    --出库入库代理点收入时间
			 scan_site_code scansitecode,
			 scan_type as  scantype,
			  send_deliver_usercode,
			 pistol_id as  pistolid
	   from jms_dwd.dwd_tab_barscan_collect_base_hi
	   where dt between date_format(date_add(substr('{{ execution_date | cst_hour }}',1,10), -1), 'yyyy-MM-dd-00')  --modify jixk 20230509
		 and date_format(date_add(substr('{{ execution_date | cst_hour }}',1,10), 1), 'yyyy-MM-dd-00')
		 and length(waybill_no) = 15
		 and scan_type in ('代理点收入扫描', '出库扫描', '入库扫描')
	   ) t
	   join t_reback_transfer  on t.billcode=t_reback_transfer.billcode
	   where t.scantime>=t_reback_transfer.reback_transfer_apply_time
) t group by billcode
),

--入库扫描
   t_collect_in as (
   select billcode,
		  case when length(min(wid_columns)[0]) = 0 then null else min(wid_columns)[0] end fst_collect_in_time,
		  case when length(min(wid_columns)[1]) = 0 then null else min(wid_columns)[1] end fst_collect_in_network_code,
		  case when length(max(wid_columns)[0]) = 0 then null else max(wid_columns)[0] end lst_collect_in_time,
		  case when length(max(wid_columns)[1]) = 0 then null else max(wid_columns)[1] end lst_collect_in_network_code,
		  case when length(max(wid_columns)[2]) = 0 then null else max(wid_columns)[2] end lst_collect_in_terminal_type,--对接终端类型 驿站or柜机
		  case when length(max(wid_columns)[3]) = 0 then null else max(wid_columns)[3] end lst_collect_in_terminal_source,--对接终端来源 快宝驿站、丰巢等
		  case when length(max(wid_columns)[4]) = 0 then null else max(wid_columns)[4] end lst_collect_in_terminal_name--对接终端名称、地址等
   from (
		 select a.waybill_no as billcode,
			   split(concat(a.scan_time,'\001',
			   coalesce(a.scan_site_code,''),'\001',
			   coalesce(a.source,''),'\001',
			   coalesce(a.partner_name,''),'\001',
			   coalesce(a.storage_address,'')
			   ),'\001') as wid_columns
		 from jms_dwd.dwd_tab_barscan_collect_base_hi a
		 join t_reback_transfer  on a.waybill_no=t_reback_transfer.billcode
		 where dt between date_format(date_add(substr('{{ execution_date | cst_hour }}',1,10), -1), 'yyyy-MM-dd-00')  --modify jixk 20230509
		   and date_format(date_add(substr('{{ execution_date | cst_hour }}',1,10), 1), 'yyyy-MM-dd-00')
		   and length(a.waybill_no) = 15
		   and a.scan_type = '入库扫描'
		) t group by billcode
),

--出库扫描
   t_collect_out as (
   select billcode,
		  case when length(min(wid_columns)[0]) = 0 then null else min(wid_columns)[0] end fst_collect_out_time,
		  case when length(min(wid_columns)[1]) = 0 then null else min(wid_columns)[1] end fst_collect_out_network_code,
		  case when length(min(wid_columns)[2]) = 0 then null else min(wid_columns)[2] end fst_collect_out_user_code,
		  case when length(max(wid_columns)[0]) = 0 then null else max(wid_columns)[0] end lst_collect_out_time,
		  case when length(max(wid_columns)[1]) = 0 then null else max(wid_columns)[1] end lst_collect_out_network_code,
		  case when length(max(wid_columns)[2]) = 0 then null else max(wid_columns)[2] end lst_collect_out_user_code,
		  case when length(max(wid_columns)[3]) = 0 then null else max(wid_columns)[3] end lst_collect_out_terminal_type,--对接终端类型 驿站or柜机
		  case when length(max(wid_columns)[4]) = 0 then null else max(wid_columns)[4] end lst_collect_out_terminal_source,--对接终端来源 快宝驿站、丰巢等
		  case when length(max(wid_columns)[5]) = 0 then null else max(wid_columns)[5] end lst_collect_out_terminal_name--对接终端名称、地址等
   from (
		 select waybill_no as billcode,
			   split(concat(scan_time,'\001',
			   coalesce(scan_site_code,''),'\001',
			   coalesce(send_deliver_usercode,''),'\001',
			   coalesce(source,''),'\001',
			   coalesce(partner_name,''),'\001',
			   coalesce(storage_address,'')
			   ),'\001') as wid_columns
		 from jms_dwd.dwd_tab_barscan_collect_base_hi
		 where dt between date_format(date_add(substr('{{ execution_date | cst_hour }}',1,10), -1), 'yyyy-MM-dd-00')  --modify jixk 20230509
		   and date_format(date_add(substr('{{ execution_date | cst_hour }}',1,10), 1), 'yyyy-MM-dd-00')
		   and length(waybill_no) = 15
		   and scan_type = '出库扫描'
		 ) t group by billcode
),

--快件取���扫描
   t_deliver_out as (
   select billcode,
		  case when length(min(wid_columns)[0]) = 0 then null else min(wid_columns)[0] end fst_deliver_out_time,
		  case when length(min(wid_columns)[1]) = 0 then null else min(wid_columns)[1] end fst_deliver_out_network_code,
		  case when length(max(wid_columns)[0]) = 0 then null else max(wid_columns)[0] end lst_deliver_out_time,
		  case when length(max(wid_columns)[1]) = 0 then null else max(wid_columns)[1] end lst_deliver_out_network_code
   from (
		 select waybill_no as billcode,
			   split(concat(scan_time,'\001',
			   coalesce(scan_site_code,'')
			   ),'\001') as wid_columns
		 from jms_dwd.dwd_tab_barscan_collect_base_hi
		 where dt between date_format(date_add(substr('{{ execution_date | cst_hour }}',1,10), -1), 'yyyy-MM-dd-00')  --modify jixk 20230509
		   and date_format(date_add(substr('{{ execution_date | cst_hour }}',1,10), 1), 'yyyy-MM-dd-00')
		   and length(waybill_no) = 15
		   and scan_type = '快件取出扫描'
		 ) t group by billcode
),

--快件签收
   t_sign as (
   select billcode,
		  case when length(max(wid_columns)[0]) = 0 then null else max(wid_columns)[0] end lst_sign_time,
		  case when length(max(wid_columns)[1]) = 0 then null else max(wid_columns)[1] end lst_sign_network_code,
		  case when length(max(wid_columns)[2]) = 0 then null else max(wid_columns)[2] end lst_sign_user_code,
		  case when length(max(wid_columns)[3]) = 0 then null else max(wid_columns)[3] end lst_sign_source
   from (
		select waybill_no as billcode,
			   split(concat(signed_time,'\001',
			   coalesce(signed_site_code,''),'\001',
			   coalesce(send_user_code,''),'\001',
			   coalesce(source,'')
			   ),'\001') as wid_columns
		from jms_dwd.dwd_tab_barscan_sign_base_hi
		where dt between date_format(date_add(substr('{{ execution_date | cst_hour }}',1,10), -1), 'yyyy-MM-dd-00')  --modify jixk 20230509
		  and date_format(date_add(substr('{{ execution_date | cst_hour }}',1,10), 1), 'yyyy-MM-dd-00')
		  and length(waybill_no) = 15
		 ) t group by billcode
),

sms_send_detail as (
select
   waybill_no,       --运单号
   source_name       --短信方式
from jms_dwd.dwd_yl_sms_send_detail_base_hi
where dt>=date_format(date_add(substr('{{ execution_date | cst_hour }}',1,10), -1), 'yyyy-MM-dd-00')  --modify jixk 20230509
and dt<date_format(date_add(substr('{{ execution_date | cst_hour }}',1,10), 1), 'yyyy-MM-dd-00')
and waybill_no is not null
and TEMPLATE_NAME='退回件自动触发'
),

---转邮   跟时效签收相关 改转邮
barscan_transfer as (
 select
	 billcode,
	 case when length(min(wid_columns)[0]) = 0 then null else min(wid_columns)[0] end fst_transfer_aging_sign_time,
	 case when length(min(wid_columns)[1]) = 0 then null else min(wid_columns)[1] end fst_transfer_aging_sign_network_code,
	 case when length(min(wid_columns)[2]) = 0 then null else min(wid_columns)[2] end fst_transfer_aging_sign_scan_type,
	 case when length(min(wid_columns)[3]) = 0 then null else min(wid_columns)[3] end fst_transfer_aging_sign_user_code,
	 case when length(min(wid_columns)[4]) = 0 then null else min(wid_columns)[4] end fst_transfer_aging_sign_pistolid,--巴枪id PDD开头是桃花岛其他是自助对接
	 case when length(max(wid_columns)[0]) = 0 then null else max(wid_columns)[0] end lst_transfer_aging_sign_time,
	 case when length(max(wid_columns)[1]) = 0 then null else max(wid_columns)[1] end lst_transfer_aging_sign_network_code,
	 case when length(max(wid_columns)[2]) = 0 then null else max(wid_columns)[2] end lst_transfer_aging_sign_scan_type,
	 case when length(max(wid_columns)[3]) = 0 then null else max(wid_columns)[3] end lst_transfer_aging_sign_user_code,
	 case when length(max(wid_columns)[4]) = 0 then null else max(wid_columns)[4] end lst_transfer_aging_sign_pistolid,
	 1 as is_transfer
 from (
		  select
			  billcode,
			  split(concat(scantime,'\001',
						   coalesce(scansitecode,''),'\001',
						   coalesce(scantype,''),'\001',
						   coalesce(send_deliver_usercode,''),'\001',
						   coalesce(pistolid,'')
						),'\001') as wid_columns
		  from(
				  select
					  bill_code as billcode,
					  scan_time as scantime,
					  scan_site_code as scansitecode,
					  case when  transfer_flag=1 then '菜鸟'
						   when  transfer_flag=2 then '邮政'
						  end  as scantype,
					  send_deliver_user_code as  send_deliver_usercode,
					  pistol_id as pistolid
				  from jms_dwd.dwd_tab_barscan_transfer_mail_base_hi
                 where dt>=date_format(date_add(substr('{{ execution_date | cst_hour }}',1,10), -15), 'yyyy-MM-dd-00')  --modify jixk 20230509
                   and dt<date_format(date_add(substr('{{ execution_date | cst_hour }}',1,10), 1), 'yyyy-MM-dd-00')
					and length(bill_code) = 15
			  )tmp
	  ) t group by billcode
),

---完结件   add jixk 2023-11-29
tab_end_piece as (
select *
  from (
select waybill_no
       ,end_code
	   ,end_code_desc
	   ,end_time
	   ,row_number() over (partition by waybill_no order by input_time) as rnk
  from jms_dwd.dwd_tab_end_piece_base_hi
 where dt>=date_format(date_add(substr('{{ execution_date | cst_hour }}',1,10), -1), 'yyyy-MM-dd-00')  --modify jixk 20230509
   and dt<date_format(date_add(substr('{{ execution_date | cst_hour }}',1,10), 1), 'yyyy-MM-dd-00')
   and is_delete=2   --否
   ) t where rnk=1
),

---
delay_deliver as(
 SELECT
	 waybill_id,operate_time ,township_name
 from (
		  select waybill_id,operate_time ,township_name,scan_network_code,scan_by_code,row_number() over(partition by waybill_id order by scan_time desc ) rn
		    from jms_dwd.dwd_tab_barscan_delay_deliver_base_hi
		   where dt>=date_format(date_add(substr('{{ execution_date | cst_hour }}',1,10), -15), 'yyyy-MM-dd-00')
             and dt<date_format(date_add(substr('{{ execution_date | cst_hour }}',1,10), 1), 'yyyy-MM-dd-00')
			 and is_township_articles=1 and township_name is not null
		  ) t where t.rn=1
)

insert overwrite table jms_dm.dm_reback_transfer_warming_waybill_detail_dt partition (dt)
select
	  final_plan_sign_time                               --规划签收时间
	 ,t.deadline_signing_time                            --规划签收截止时间
	 ,end_center_send_time                               --末端中心/集散发件时间
	 ,t_network2.manage_code as end_manage_region_code   --末端网点所属管理大区编码
	 ,t_network2.manage_name as end_manage_region_name   --末端网点所属管理大区名称
	 ,t_network2.provider_id as end_provider_id          --末端网点所属省份id
	 ,t_network2.provider_desc as end_provider_name      --末端网点所属省份名称
	 ,t_network2.city_id as end_city_id                  --末端网点所属城市id
	 ,t_network2.city_desc as end_city_name              --末端网点所属城市名称
	 ,t_network2.area_id as end_area_id                  --末端网点所属区县id
	 ,end_center_code                                    --末端发件中心/集散编码
	 ,t_network1.name as end_center_name                 --末端发件中心/集散名称
	 ,t_network2.agent_code as end_agent_code            --末端网点所属代理区编码
	 ,t_network2.agent_name as end_agent_name            --末端网点所属代理区名称
	 ,t_network2.fran_code as end_franchisee_code        --末端网点所属加盟商编码
	 ,t_network2.fran_name as end_franchisee_name        --末端网点所属加盟商名称
	 ,t.end_network_code                                 --末端到件网点编码
	 ,t_network4.name as end_network_name                --末端到件网点名称
	 ,send_next_station_code                             --末端中心/集散发件下一站编码
	 ,t_network3.name as send_next_station_name          --末端中心/集散发件下一站名称
	 ,aging_sign_network_code                            --时效签收网点编码
	 ,t_network6.name as aging_sign_network_name         --时效签收网点名称
	 ,final_sign_network_code                            --规划签收网点编码
	 ,t_network2.name as final_sign_network_name      --规划签收网点名称
	 ,t.billcode                                      --运单号
	 ,t.deliver_shift                                 --派件派仓班次
	 ,t.entry_type                                     --退件类型
	 ,t.reback_transfer_apply_time                    --退件登记时间
	 ,t.apply_network_code                            --登记网点编码
	 ,t.apply_network_name                            --登记网点名称
	 ,t.print_network_code                            --面单打印网点编码
	 ,t.print_network_name                            --面单打印网点名称
	 ,t.print_time                                    --面单打印时间
	 ,deliver_time                                    --出仓时间
	 ,aging_sign_time                                 --时效签收时间
	 ,actual_sign_time                                --实际签收时间
	 ,deliver_township_id                             --出仓乡镇id
	 ,t_area.cn_name as deliver_township_name         --出仓乡镇名称
	 ,t.special_extra_time                            --跨越天数_出仓乡镇
	 ,deliver_user_code                               --出仓派件员编码
	 ,final_sign_user_code                            --最后签收派件员编码
	 ,t_staff.name as final_sign_user_name            --最后签收派件员名称
	 ,if(aging_sign_time <= concat_ws(' ',date_format(final_plan_sign_time, 'yyyy-MM-dd'),'22:00:00'),1,0) is_aging_sign_22  --22点前时效签��准点 1准点 0不准点
	 ,is_message  --是否短信
	 ,source_name     --短信方式
	 ,end_network_arrival_time --末端网点到件时间
     ,aging_sign_scan_type                             as sign_type           ---签收类型      add jixk 2023-11-29
	 ,case when aging_sign_time is not null then '1'
	       when aging_sign_time is null and end_time is not null and end_code in ('5','30','41','44') then '2'
           else '0'
	  end                                              as sign_res_type       ---签收结果      add jixk 2023-11-29
     ,is_arrival_send                                  as is_arrival_send     ---是否到出一体
	 ,concat('{{ execution_date | cst_hour_ds }}',':00:00')                 as etl_update_time     ---数据更新时间  add jixk 2023-11-29
	 ,end_center_send_date as dt                                              --末端中心发件日期
from (
	select t.*,
           case when to_date(if(is_delay_deliver=1,end_network_arrival_time,deliver_time)) <=to_date(effective_plan_sign_time) then (
                case

                 when deadline_signing_time is not null and special_extra_time=0.5 and deadline_signing_time <='11:30:00'
                                  then concat(to_date(effective_plan_sign_time), ' 22:00:00')
                              when deadline_signing_time is not null and special_extra_time=0.5 and deadline_signing_time>'11:30:00'
                                  then concat(date_add(to_date(effective_plan_sign_time), 1), ' 10:30:00')
                     else special_plan_sign_time end )
                  else effective_plan_sign_time end  final_plan_sign_time,
		   date_format((case when reback_plan_sign_time is not null then reback_plan_sign_time when deliver_time <= effective_plan_sign_time then special_plan_sign_time else effective_plan_sign_time end), 'yyyy-MM-dd') as final_plan_sign_date
	from (
		  select

			   t.billcode                     --运单号
			  ,t.end_center_send_date        --末端中心发件日期
			  ,t.end_center_send_date_hours  --末端中心发件日期小时 yyyy-MM-dd-00-HH
			  ,t.end_center_code             --末端中心code
			  ,t.end_center_send_time        --末端中心发件时间
			  ,t.send_next_station_code      --末端网点code
			  ,t.fst_end_center_send_time    --第一次末端中心发件时间
			  ,t.fst_end_center_code         --第一次末端中心code
			  ,t.fst_send_next_station_code  --第一次末端网点code
			  ,t.lst_end_center_send_time    --最后一次末端中心发件时间
			  ,t.lst_end_center_code         --最后一次末端中心code
			  ,t.lst_send_next_station_code   --最后一次末端网点code
			  ,t.end_center_send_hms           --末端中心发件时间 HH:mm:ss
			  ,t.final_sign_network_code --最终确定的签收网点(派件网点),出库之后没有快件取出则取出库网点,没有出库则取快件签收,都没有取中心集散发件下一站
			  ,t.end_network_code      --相同网点多次到件去最早，不同网点多次到��取最晚
			  ,t.end_network_arrival_time --末端网点到件时间
			  ,t.deliver_network_id      --出仓网点id 中心集散发件之前的出仓不算,否则相同网点多次出仓取最早，不同网点多次出仓取最晚
			  ,t.deliver_network_code    --出仓网点code
			  ,t.deliver_time            --出仓时间
			  ,t.deliver_user_code       --派件员code
			  ,t.deliver_unrecordflag    --未录单标识
			  ,t.deliver_township_id     --末端乡镇id
			  ,t.aging_sign_network_code -- 时效签收网点code   相同网点多次出仓时效签收取最早，不同网点多次出仓时效签收取最晚
			  ,t.aging_sign_time        ---时效签收时间
			  ,t.aging_sign_scan_type   --时效签收类型
			  ,t.aging_sign_user_code   --时效签收派件员code
			  ,t.aging_sign_pistolid    --时效签收巴枪 id
			  ,t.actual_sign_network_code   --出库之后没有快件取出则取出库网点,没有出库则取快件签收网点作为实际签收网点
			  ,t.actual_sign_time    --实际签收时间
			  --,t.actual_sign_scan_type  --实际签收类型
			  ,t.actual_sign_user_code   --实际派件员code
			  ,t.is_arrival_repeatedly  --是否二次到达
			  ,t.is_deliver_repeatedly   --是否二次派
			  ,t.collect_in_network_code  --入库网点编码
			  ,t.collect_in_time           --入库时间
			  ,t.collect_out_network_code  --出库网点编码
			  ,t.collect_out_time           --出库时间
			  ,t.terminal_type              --对接终端类型 驿站or柜机
			  ,t.terminal_source     --对接终端来源 快宝驿站、丰巢等
			  ,t.terminal_name       --对接终端名称、地址等
			  ,t.deliver_out_network_code   --快件取出网点编码
			  ,t.deliver_out_time      --快件取出
			  ,t.sign_network_code     --快件签收网点
			  ,t.sign_time
			  ,t.print_network_code          --面单打印网点编码
			  ,t.print_network_name          --面单打印网点名称
			  ,t.reback_transfer_apply_time  --退件登记时间
			  ,t.entry_type                  --退件类型
			  ,t.apply_network_code          --登记网点编码
			  ,t.apply_network_name          --登记网点名称
			  ,t.print_time                  --面单打印时间
			  ,planned_delivery_time
			  ,nvl(deadline_signing_time,'22:00:00') as deadline_signing_time
			  ,span_days_arrive--跨越天数_到车
			  ,out_line_day --派件班次-支线衔接跨天
			  ,span_days_shift --派件跨越天数
			  --,span_days_sign --跨越天数_签收
			  ,extra_time as special_extra_time --跨越天数_出仓乡镇
			  ,nvl(t_effective.shift,0) as deliver_shift --班次
			  ,is_message  --是否短信
			  ,source_name     --短信方式
			  ,case when (actual_sign_user_code is null or length(actual_sign_user_code) = 0) then deliver_user_code else actual_sign_user_code end final_sign_user_code --优先取实际签收派件员,其次取出仓派件员
			  ,case when end_center_send_time < lst_end_network_send_time and actual_sign_time is not null then concat_ws(' ',date_format(actual_sign_time,'yyyy-MM-dd'),'22:00:00') else null end reback_plan_sign_time --退回件退回之后,末端中心集散漏扫发件,则无法计算规划签收时间,以实际签收日期23:59:59为规划签收时间
			  ,concat_ws(' ',date_format(date_add(end_center_send_date,if(end_center_send_hms > planned_delivery_time,1,0) + nvl(span_days_arrive,0) + nvl(out_line_day,0) + nvl(span_days_shift,0)),'yyyy-MM-dd'),nvl(deadline_signing_time,'22:00:00')) as effective_plan_sign_time --没有乡镇加时的规划签收时间,若取不到对应班次,则以中心集散发件日期23:59:59作为规划签收时间
			  ,from_unixtime(cast(unix_timestamp(concat_ws(' ', date_format(date_add(end_center_send_date,if(end_center_send_hms > planned_delivery_time,1,0)
			  + nvl(span_days_arrive, 0) + nvl(out_line_day, 0) + nvl(span_days_shift, 0)), 'yyyy-MM-dd'), nvl(deadline_signing_time, '22:00:00'))) + nvl(extra_time, 0) * 24 * 60 * 60 as bigint), 'yyyy-MM-dd HH:mm:ss') as special_plan_sign_time--有乡镇加时的规划签收时间
----			  ,row_number() over (partition by billcode order by get_span_time(end_center_send_hms,planned_delivery_time) asc) bill_rank
          ---modify jixk 2023-11-29
              ,row_number() over (partition by billcode order by if(end_center_send_hms > planned_delivery_time, unix_timestamp(planned_delivery_time,'HH:mm:ss')+ 24*60*60 - unix_timestamp(end_center_send_hms,'HH:mm:ss'),unix_timestamp(planned_delivery_time,'HH:mm:ss') - unix_timestamp(end_center_send_hms,'HH:mm:ss')),nvl(t_effective.shift,0) asc,deadline_signing_time desc ) bill_rank
			  ,is_end_piece
			  ,end_code
			  ,end_code_desc
			  ,end_time
              ,end_network_source		 --末端网点到件扫描来源
              ,site_town_id              --到派一体出仓乡镇
              ,deliver_scan_user_code    --新增出仓扫描员code
              ,deliver_check_flag        --新增核算标识
              ,if(deliver_check_flag in (7,9,10,11,12,13),1,0) as is_arrival_send --是否到���一体
		       ,delay_deliver_township_id
		     ,is_delay_deliver
		  from (
				select
						t_send.billcode                    --运单号
					   ,t_send.end_center_send_time        --末端中心发件时间
					   ,t_send.end_center_code             --末端中心code
					   ,t_send.send_next_station_code      --末端网点code
					   ,t_send.fst_end_center_send_time    --第一次末端中心发件时间
					   ,t_send.fst_end_center_code         --第一次末端中心code
					   ,t_send.fst_send_next_station_code  --第一次末端网点code
					   ,t_send.lst_end_center_send_time    --最后一次末端中心发件时间
					   ,t_send.lst_end_center_code         --最后一次末端中心code
					   ,t_send.lst_send_next_station_code   --最后一次末端网点code

					   ,date_format(end_center_send_time, 'yyyy-MM-dd') end_center_send_date
					   ,date_format(end_center_send_time, 'yyyy-MM-dd-HH') end_center_send_date_hours,
					   date_format(end_center_send_time, 'HH:mm:ss')    end_center_send_hms,
					   nvl((case when lst_collect_out_time is not null and (lst_deliver_out_time is null or lst_collect_out_time > lst_deliver_out_time) then lst_collect_out_network_code
					   else lst_sign_network_code end),send_next_station_code) as final_sign_network_code,--最终确定的签收网点(派件网点),出库之后没有快件取出则取出库网点,没有出库则取快件签收,都没有取中心集散发件下一站
					   case when nvl(is_arrival_repeatedly,2) = 2 then fst_end_network_code else lst_end_network_code end end_network_code,--相同网点多次到件去最早，不同网点多次到件取最晚
					   case when nvl(is_arrival_repeatedly,2) = 2 then fst_end_network_arrival_time else lst_end_network_arrival_time end end_network_arrival_time,
                       case when nvl(is_arrival_repeatedly,2) = 2 then fst_end_network_source else lst_end_network_source end end_network_source,				---add jixk 20231129
                       case when nvl(is_arrival_repeatedly,2) = 2 then fst_deliver_township_id else lst_deliver_township_id end site_town_id,  --新增d到派一体出仓乡镇id	    ---add jixk 20231129
					   case when end_center_send_time > lst_deliver_time then null when nvl(is_deliver_repeatedly,2) = 2 then fst_deliver_network_id else lst_deliver_network_id end deliver_network_id,--中心集散发件之前的出仓不算,否则相同网点多次出仓取最早，不同网点多次出仓取最晚
					   case when end_center_send_time > lst_deliver_time then null when nvl(is_deliver_repeatedly,2) = 2 then fst_deliver_network_code else lst_deliver_network_code end deliver_network_code,
					   case when end_center_send_time > lst_deliver_time then null when nvl(is_deliver_repeatedly,2) = 2 then fst_deliver_time else lst_deliver_time end deliver_time,
					   case when end_center_send_time > lst_deliver_time then null when nvl(is_deliver_repeatedly,2) = 2 then fst_deliver_user_code else lst_deliver_user_code end deliver_user_code,
					   case when end_center_send_time > lst_deliver_time then null when nvl(is_deliver_repeatedly,2) = 2 then fst_deliver_unrecordflag else lst_deliver_unrecordflag end deliver_unrecordflag,

					  case when end_center_send_time > lst_deliver_time then null when nvl(is_deliver_repeatedly,2) = 2 then fst_deliver_scan_user_code else lst_deliver_scan_user_code end deliver_scan_user_code,  --新增出仓扫描员code   add jixk 20231129
					  case when end_center_send_time > lst_deliver_time then null when nvl(is_deliver_repeatedly,2) = 2 then fst_deliver_check_flag else lst_deliver_check_flag end deliver_check_flag,  --新增出仓核算标识			add jixk 20231129
					   case when end_center_send_time > lst_deliver_time then null when nvl(is_deliver_repeatedly,2) = 2 then fst_deliver_township_id else lst_deliver_township_id end deliver_township_id,

					  case when nvl(barscan_transfer.is_transfer,0)=1 then barscan_transfer.fst_transfer_aging_sign_network_code--新增      ---modify jixk 20231129增加转邮
						   when nvl(is_aging_repeatedly,2) = 2 then fst_aging_sign_network_code else lst_aging_sign_network_code end aging_sign_network_code,--相同网点多次出仓时效签收取最早，不同网点多次出仓时效签收取最晚
				      case when nvl(barscan_transfer.is_transfer,0)=1 then barscan_transfer.fst_transfer_aging_sign_time--新增
						   when nvl(is_aging_repeatedly,2) = 2 then fst_aging_sign_time else lst_aging_sign_time end aging_sign_time,
					  case when nvl(barscan_transfer.is_transfer,0)=1 then barscan_transfer.fst_transfer_aging_sign_scan_type --新增
						   when nvl(is_aging_repeatedly,2) = 2 then fst_aging_sign_scan_type else lst_aging_sign_scan_type end aging_sign_scan_type,
					  case when nvl(barscan_transfer.is_transfer,0)=1 then barscan_transfer.fst_transfer_aging_sign_user_code --新增
						   when nvl(is_aging_repeatedly,2) = 2 then fst_aging_sign_user_code else lst_aging_sign_user_code end aging_sign_user_code,
					  case when nvl(barscan_transfer.is_transfer,0)=1 then barscan_transfer.fst_transfer_aging_sign_pistolid   --新��
						   when nvl(is_aging_repeatedly,2) = 2 then fst_aging_sign_pistolid else lst_aging_sign_pistolid end aging_sign_pistolid,

					   case when lst_collect_out_time is not null and (lst_deliver_out_time is null or lst_collect_out_time > lst_deliver_out_time) then lst_collect_out_network_code
						   else lst_sign_network_code end actual_sign_network_code,--出库之后没有快件取出则取出库网点,没有出库则取快件签收网点作为实际签收网点
					  case when lst_collect_out_time is not null and (lst_deliver_out_time is null or lst_collect_out_time > lst_deliver_out_time) then lst_collect_out_time
						   else lst_sign_time end actual_sign_time,
					 case when lst_collect_out_time is not null and (lst_deliver_out_time is null or lst_collect_out_time > lst_deliver_out_time) then lst_collect_out_user_code
						   else lst_sign_user_code end actual_sign_user_code,
					   nvl(is_arrival_repeatedly,2) as is_arrival_repeatedly,
					   nvl(is_deliver_repeatedly,2) as is_deliver_repeatedly,
					   --lst_proxy_in_network_code as proxy_in_network_code,
					   --lst_proxy_in_network_name as proxy_in_network_name,
					   --lst_proxy_in_time as proxy_in_time,
					   lst_collect_in_network_code as collect_in_network_code,
					   lst_collect_in_time as collect_in_time,
					   lst_collect_out_network_code as collect_out_network_code,
					   lst_collect_out_time as collect_out_time,
					   nvl(lst_collect_in_terminal_type,lst_collect_out_terminal_type) as terminal_type,
					   nvl(lst_collect_in_terminal_source,lst_collect_out_terminal_source) as terminal_source,
					   nvl(lst_collect_in_terminal_name,lst_collect_out_terminal_name) as terminal_name,
					   lst_deliver_out_network_code as deliver_out_network_code,
					   lst_deliver_out_time as deliver_out_time,
					   lst_sign_network_code as sign_network_code,--快件签收网点
					   lst_sign_time as sign_time,
					   lst_sign_source as sign_source,
					   t_reback_transfer.print_network_code,         --面单打印��点编码
					   t_reback_transfer.print_network_name,         --面单打印网点名称
					   t_reback_transfer.reback_transfer_apply_time, --退件登记时间
					   t_reback_transfer.lst_end_network_send_time  ---退转后网点发件时间
					   ,t_reback_transfer.entry_type                 --退件类型
					   ,t_reback_transfer.apply_network_code         --登记网点编码
					   ,t_reback_transfer.apply_network_name         --登记网点名称
					   ,t_reback_transfer.print_time  --面单打印时间
					   ,if(sms_send_detail.WAYBILL_NO is not null,1,0) as is_message   --是否短信
					   ,sms_send_detail.source_name     --短信方式
					   ,if(tab_end_piece.waybill_no is not null,1,0) as is_end_piece   --是否完结件
					   ,tab_end_piece.end_code                       as end_code
                       ,tab_end_piece.end_code_desc                  as end_code_desc
					   ,tab_end_piece.end_time	                     as end_time
					   ,delay_deliver.township_name as delay_deliver_township_id
					   ,delay_deliver.operate_time as delay_deliver_time
					--
					   ,case when delay_deliver.waybill_id is not null then 1 else 0 end is_delay_deliver
				from t_send
				join t_reback_transfer on t_send.billcode = t_reback_transfer.billcode
				left join t_arrival on t_send.billcode = t_arrival.billcode--网点到件
				left join t_deliver on t_send.billcode = t_deliver.billcode--出仓
				left join delay_deliver on t_send.billcode=delay_deliver.waybill_id
				and delay_deliver.operate_time >= t_send.end_center_send_time
				left join t_aging_sign on t_send.billcode = t_aging_sign.billcode--时效签收
				left join t_collect_in on t_send.billcode = t_collect_in.billcode--入库
				left join t_collect_out on t_send.billcode = t_collect_out.billcode--出库
				left join t_deliver_out on t_send.billcode = t_deliver_out.billcode--派件员取出
				left join t_sign on t_send.billcode = t_sign.billcode--快件签收
				left join sms_send_detail on  t_send.billcode=sms_send_detail.waybill_no
				left join tab_end_piece on  t_send.billcode=tab_end_piece.waybill_no       ---完结件
				left join barscan_transfer on t_send.billcode=barscan_transfer.billcode  ---转邮

				) t
		  left join t_effective ---on end_center_send_date = t_effective.create_date
								on   end_center_code = depart_network_code and final_sign_network_code = arrive_network_code
		  left join t_special on deliver_network_id = t_special.network_id and deliver_township_id = t_special.township_id and t_effective.shift = t_special.shift
		  ) t
	where bill_rank = 1
	) t
left join t_area on COALESCE(deliver_township_id,site_town_id,delay_deliver_township_id) = t_area.id--出仓乡镇
left join t_staff on final_sign_user_code = t_staff.code--派件员信息
left join t_network t_network1 on end_center_code = t_network1.code
left join t_network t_network2 on final_sign_network_code = t_network2.code
left join t_network t_network3 on send_next_station_code = t_network3.code
left join t_network t_network4 on end_network_code = t_network4.code
left join t_network t_network5 on deliver_network_code = t_network5.code
left join t_network t_network6 on aging_sign_network_code = t_network6.code
left join t_network t_network7 on actual_sign_network_code = t_network7.code
;


insert overwrite table jms_dm.dm_reback_transfer_warming_type_waybill_detail_dt partition (dt)
select final_plan_sign_time         --规划签收时间
       ,substr(final_plan_sign_time,12,8) as deadline_signing_time        --规划签收截止时间
       ,end_center_send_time         --末端中心
       ,end_manage_region_code       --末端网点所属管理大区编码
       ,end_manage_region_name       --末端网点所属管理大区名称
       ,end_provider_id              --末端网点所属省份id
       ,end_provider_name            --末端网点所属省份名称
       ,end_city_id                  --末端网点所属城市id
       ,end_city_name                --末端网点所属城市名称
       ,end_area_id                  --末端网点所属区县id
       ,end_center_code              --末端发件中心
       ,end_center_name              --末端发件中心
       ,end_agent_code               --末端网点所属代理区编码
       ,end_agent_name               --末端网点所属代理区名称
       ,end_franchisee_code          --末端网点所属加盟商编码
       ,end_franchisee_name          --末端网点所属加盟商名称
       ,end_network_code             --末端到件网点编码
       ,end_network_name             --末端到件网点名称
       ,send_next_station_code       --末端中心/集散发件下一站编码
       ,send_next_station_name       --末端中心/集散发件下一站编码
       ,billcode                     --运单号
       ,deliver_shift                --派件派仓班次
       ,entry_type                   --退件类型
       ,reback_transfer_apply_time   --退件登记时间
       ,apply_network_code           --登记网点编码
       ,apply_network_name           --登记网点名称
       ,print_network_code           --面单打印网点编码
       ,print_network_name           --面单打印网点名称
       ,print_time                   --面单打印时间
       ,deliver_time                 --出仓时间
       ,aging_sign_time              --时效签收时间
       ,actual_sign_time             --实际签收时间
       ,deliver_township_id          --出仓乡镇id
       ,deliver_township_name        --出仓乡镇名称
       ,special_extra_time           --跨越天数_出仓乡镇
       ,deliver_user_code            --出仓派件员编码
       ,final_sign_user_code         --最后签收派件员编码
       ,final_sign_user_name         --最后签收派件员名称
       ,is_aging_sign_22            --是否是22点时效签收准点
       ,if(aging_sign_time > concat_ws(' ',date_format(final_plan_sign_time, 'yyyy-MM-dd'),'22:00:00') and aging_sign_time <= concat_ws(' ',date_format(final_plan_sign_time, 'yyyy-MM-dd'),'23:59:59'),1,0) is_aging_sign_24  -- 22-24点前时效签收准点 1准点 0不准点
       ,if(aging_sign_time >final_plan_sign_time and aging_sign_time >concat_ws(' ',date_format(final_plan_sign_time, 'yyyy-MM-dd'),'22:00:00') and aging_sign_time is not null ,1,0) after_aging_sign_22  -- 22点后延误签收 1延误 0不延误
       ,2 as type1
       ,to_date(end_center_send_time) as date_time   --规划签收日期
       ,is_message    --是否短信
       ,source_name  --短信模板
       ,end_network_arrival_time --末端网点到件时间
       ,if(aging_sign_time is not null and aging_sign_time <=final_plan_sign_time ,1,0) as aging_sign_on_time  --是否是时效签收
       ,if(actual_sign_time is not null and actual_sign_time <=final_plan_sign_time ,1,0) as actual_sign_on_time   --是否实际签收
       ,final_sign_network_code   -- 规划签收网点code
       ,final_sign_network_name   --规划签收网点名字
	   ,sign_type
	   ,sign_res_type
	   ,is_arrival_send
	   ,etl_update_time
       ,to_date(end_center_send_time) as dt
  from jms_dm.dm_reback_transfer_warming_waybill_detail_dt
 where dt>=date_format(date_add(substr('{{ execution_date | cst_hour }}',1,10), -1), 'yyyy-MM-dd')
   and dt<=date_format(date_add(substr('{{ execution_date | cst_hour }}',1,10), 0), 'yyyy-MM-dd')
   and print_time< end_center_send_time
   and to_date(end_center_send_time)>= date_add(substr('{{ execution_date | cst_hour }}',1,10),-1)
   and to_date(end_center_send_time)<= date_add(substr('{{ execution_date | cst_hour }}',1,10),0)
;



insert overwrite table jms_dm.dm_reback_transfer_warming_send_sum_dt partition (dt)
select *
  from
(select max(end_manage_region_code)as manage_code                          --管理大区code
       ,max(end_manage_region_name) as manage_name                        --管理大区名字
       ,max(end_agent_code) as agent_code                                 --代理区code
       ,max(end_agent_name) as agent_name                                 --代理区名字
       ,max(end_franchisee_code) as franchisee_code                       --加盟商code
       ,max(end_franchisee_name) as franchisee_name                       --加盟商名字
       ,final_sign_network_code as delivery_network_code                   --派件网点code
       ,max(final_sign_network_name) as delivery_network_name             --派件网点名字
       ,count(1) as back_sign_cnt                                         --应签收量
       ,sum(is_aging_sign_22) as before_sign_cnt                          --22点前签收量
       ,sum(after_aging_sign_22) as after_sign_cnt                        --22点后延误签收
       ,sum(is_aging_sign_24) as zypt_cnt                                 --22点到24点签收量
       ,sum(if(aging_sign_time is null,1,0)) as no_sign_cnt               --未签收量
       ,sum(if(deliver_shift=1,1,0)) as ypy_cnt                           --一班应签收量
       ,sum(if(deliver_shift=1 and aging_sign_time<=final_plan_sign_time ,1,0))as ypz_cnt   --一班准点签收的量
       ,sum(if(deliver_shift=2,1,0)) as epy_cnt                                             --二班应签收的量
       ,sum(if(deliver_shift=2 and aging_sign_time<=final_plan_sign_time ,1,0))as epz_cnt   --二班准点签收的量
       ,sum(if(deliver_shift=3,1,0)) as spy_cnt                                             --三班应签收的量
       ,sum(if(deliver_shift=3 and aging_sign_time<=final_plan_sign_time ,1,0))as spz_cnt   -- 三班准点签收的量
       ,null as business_type                                                              --类型 1 发件 2 签收
       ,dt as date_time                                                                     --业务日期
       ,sum(if(deadline_signing_time= '10:30:00',1,0 )) as need_sign_cnt_1030                                                            -- 10：30应签收
       ,sum(if(deadline_signing_time= '10:30:00' and aging_sign_time<= final_plan_sign_time,1,0 )) as aging_sign_cnt_1030                -- 10：30准点签收
       ,sum(if(deadline_signing_time= '11:30:00',1,0 )) as need_sign_cnt_1130                                                            -- 11：30应签收
       ,sum(if(deadline_signing_time= '11:30:00' and aging_sign_time <= final_plan_sign_time,1,0 )) as aging_sign_cnt_1130               -- 11：30准点签收
       ,sum(if(deadline_signing_time= '14:00:00',1,0 )) as need_sign_cnt_1400                                                            -- 14：00应签收
       ,sum(if(deadline_signing_time= '14:00:00' and aging_sign_time <= final_plan_sign_time,1,0 )) as aging_sign_cnt_1400               -- 14：00准点签收
       ,sum(if(deadline_signing_time= '18:00:00',1,0 )) as need_sign_cnt_1800                                                            -- 18：00应签收
       ,sum(if(deadline_signing_time= '18:00:00' and aging_sign_time <= final_plan_sign_time,1,0 )) as aging_sign_cnt_1800               -- 18：00准点签收
       ,sum(if(deadline_signing_time= '22:00:00',1,0)) as need_sign_cnt_2200                                                              -- 22:00应签收量
       ,sum(if(deadline_signing_time= '22:00:00' and aging_sign_time <= final_plan_sign_time ,1,0)) as aging_sign_cnt_2200                -- 22:00准点签收量
       ,sum(if(aging_sign_time is null ,1,0)) as         no_sign_cunt
       ,sum(if(end_network_arrival_time is null,1,0)) as no_arrival_cnt
       ,sum(if(deliver_time is null ,1,0)) as            no_deliver_cnt
       ,sum(if(deadline_signing_time= '10:30:00' and aging_sign_time is null,1,0 )) as aging_no_sign_cnt_1030   --10：30未签收量
       ,sum(if(deadline_signing_time= '11:30:00' and aging_sign_time is null,1,0 )) as aging_no_sign_cnt_1130   --11：30未签收量
       ,sum(if(deadline_signing_time= '14:00:00' and aging_sign_time is null,1,0 )) as aging_no_sign_cnt_1400   --14：00未签收量
       ,sum(if(deadline_signing_time= '18:00:00' and aging_sign_time is null,1,0 )) as aging_no_sign_cnt_1800   --18：00未签收量
       ,sum(if(deadline_signing_time= '22:00:00' and aging_sign_time is null,1,0 )) as aging_no_sign_cnt_2200   --22：00未签收量
	   ,etl_update_time
       ,dt                       --分区
  from jms_dm.dm_reback_transfer_warming_type_waybill_detail_dt
 where dt >= date_add(substr('{{ execution_date | cst_hour }}',1,10),-1)
   and dt <= date_add(substr('{{ execution_date | cst_hour }}',1,10),0)
 group by dt,final_sign_network_code,etl_update_time
) t
distribute by dt
;

--签收维度

insert overwrite table jms_dm.dm_reback_transfer_warming_sign_sum_dt partition (dt)
select *
  from (
select max(end_manage_region_code)as manage_code                          --管理大区code
       ,max(end_manage_region_name) as manage_name                        --管理大区名字
       ,max(end_agent_code) as agent_code                                 --代理区code
       ,max(end_agent_name) as agent_name                                 --代理区名字
       ,max(end_franchisee_code) as franchisee_code                       --加盟商code
       ,max(end_franchisee_name) as franchisee_name                       --加盟商名字
       ,max(final_sign_network_code) as delivery_network_code             --派件网点code
       ,max(final_sign_network_name) as delivery_network_name            --派件网点名字
       ,count(1) as back_sign_cnt                                         --应签收量
       ,sum(is_aging_sign_22) as before_sign_cnt                          --22点前签收量
       ,sum(after_aging_sign_22) as after_sign_cnt                        --22点后延误签收
       ,sum(is_aging_sign_24) as zypt_cnt                                 --22点到24点签收量
       ,sum(if(aging_sign_time is null,1,0)) as no_sign_cnt               --未签收量
       ,sum(if(deliver_shift=1,1,0)) as ypy_cnt                           --一班应签收量
       ,sum(if(deliver_shift=1 and aging_sign_time<=final_plan_sign_time ,1,0))as ypz_cnt   --一班准点签收的量
       ,sum(if(deliver_shift=2,1,0)) as epy_cnt                                             --二班应签收的量
       ,sum(if(deliver_shift=2 and aging_sign_time<=final_plan_sign_time ,1,0))as epz_cnt   --二班准点签收的量
       ,sum(if(deliver_shift=3,1,0)) as spy_cnt                                             --三班应签收的量
       ,sum(if(deliver_shift=3 and aging_sign_time<=final_plan_sign_time ,1,0))as spz_cnt   -- 三班准点签收的量
       ,null as business_type                                                              --类型 1 发件 2 签收
       ,to_date(final_plan_sign_time) as date_time                                                                     --业务日期
       ,sum(if(deadline_signing_time= '10:30:00',1,0 )) as need_sign_cnt_1030                                                            -- 10：30应签收
       ,sum(if(deadline_signing_time= '10:30:00' and aging_sign_time<= final_plan_sign_time,1,0 )) as aging_sign_cnt_1030                -- 10：30准点签收
       ,sum(if(deadline_signing_time= '11:30:00',1,0 )) as need_sign_cnt_1130                                                            -- 11：30应签收
       ,sum(if(deadline_signing_time= '11:30:00' and aging_sign_time <= final_plan_sign_time,1,0 )) as aging_sign_cnt_1130               -- 11：30准点签收
       ,sum(if(deadline_signing_time= '14:00:00',1,0 )) as need_sign_cnt_1400                                                            -- 14：00应签收
       ,sum(if(deadline_signing_time= '14:00:00' and aging_sign_time <= final_plan_sign_time,1,0 )) as aging_sign_cnt_1400               -- 14：00准点签收
       ,sum(if(deadline_signing_time= '18:00:00',1,0 )) as need_sign_cnt_1800                                                            -- 18：00应签收
       ,sum(if(deadline_signing_time= '18:00:00' and aging_sign_time <= final_plan_sign_time,1,0 )) as aging_sign_cnt_1800               -- 18：00准点签收
       ,sum(if(deadline_signing_time= '22:00:00',1,0)) as need_sign_cnt_2200                                                              -- 22:00应签收量
       ,sum(if(deadline_signing_time= '22:00:00' and aging_sign_time <= final_plan_sign_time ,1,0)) as aging_sign_cnt_2200                -- 22:00准点签收量
       ,sum(if(aging_sign_time is null ,1,0)) as         no_sign_cunt
       ,sum(if(end_network_arrival_time is null,1,0)) as no_arrival_cnt
       ,sum(if(deliver_time is null ,1,0)) as            no_deliver_cnt
       ,sum(if(deadline_signing_time= '10:30:00' and aging_sign_time is null,1,0 )) as aging_no_sign_cnt_1030   --10：30未签收量
       ,sum(if(deadline_signing_time= '11:30:00' and aging_sign_time is null,1,0 )) as aging_no_sign_cnt_1130   --11：30未签收量
       ,sum(if(deadline_signing_time= '14:00:00' and aging_sign_time is null,1,0 )) as aging_no_sign_cnt_1400   --14：00未签收量
       ,sum(if(deadline_signing_time= '18:00:00' and aging_sign_time is null,1,0 )) as aging_no_sign_cnt_1800   --18：00未签收量
       ,sum(if(deadline_signing_time= '22:00:00' and aging_sign_time is null,1,0 )) as aging_no_sign_cnt_2200   --22：00未签收量
	   ,etl_update_time
       ,to_date(final_plan_sign_time) as dt                       --分区
 from jms_dm.dm_reback_transfer_warming_type_waybill_detail_dt
where dt >= date_add(substr('{{ execution_date | cst_hour }}',1,10),-8)
  and  dt <= date_add(substr('{{ execution_date | cst_hour }}',1,10),-0)
  and to_date(final_plan_sign_time)>=date_add(substr('{{ execution_date | cst_hour }}',1,10),-1)
  and to_date(final_plan_sign_time)<=date_add(substr('{{ execution_date | cst_hour }}',1,10),0)
group by to_date(final_plan_sign_time),final_sign_network_code,etl_update_time
) t
distribute by dt
;